from flask import Flask, render_template, request, jsonify, session, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
from flask_jwt_extended import JWTManager, create_access_token, jwt_required, get_jwt_identity
from werkzeug.security import generate_password_hash, check_password_hash
import datetime
import random
import string

# 初始化Flask应用
app = Flask(__name__, static_folder='static', template_folder='templates')

# 配置数据库
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@localhost:3306/bankdb'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = 'your-secret-key'
app.config['JWT_SECRET_KEY'] = 'jwt-secret-key'

# 初始化扩展
db = SQLAlchemy(app)
jwt = JWTManager(app)


# 数据模型定义
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    password_hash = db.Column(db.String(512), nullable=False)
    role = db.Column(db.String(20), default='admin')
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)


class Customer(db.Model):
    customer_id = db.Column(db.Integer, primary_key=True)
    customer_name = db.Column(db.String(100), nullable=False)
    id_card = db.Column(db.String(20), unique=True, nullable=False)
    phone = db.Column(db.String(20), nullable=False)
    address = db.Column(db.String(200))
    email = db.Column(db.String(100))
    cards = db.relationship('Card', backref='customer', lazy=True)


class Card(db.Model):
    card_id = db.Column(db.Integer, primary_key=True)
    card_number = db.Column(db.String(19), unique=True, nullable=False)
    customer_id = db.Column(db.Integer, db.ForeignKey('customer.customer_id'), nullable=False)
    deposit_type = db.Column(db.String(20), nullable=False)  # 活期/定期
    balance = db.Column(db.Float, default=0.0)
    open_date = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    password = db.Column(db.String(20), default='888888')  # 简化实现，实际应加密
    transactions = db.relationship('Transaction', backref='card', lazy=True)


class Transaction(db.Model):
    transaction_id = db.Column(db.Integer, primary_key=True)
    card_number = db.Column(db.String(19), db.ForeignKey('card.card_number'), nullable=False)
    transaction_type = db.Column(db.String(20), nullable=False)  # 存入/支取
    transaction_amount = db.Column(db.Float, nullable=False)
    transaction_date = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    remark = db.Column(db.String(200))


# 测试数据库连接的路由
@app.route('/test-db')
def test_db():
    try:
        result = db.engine.execute('SELECT 1')
        return f"数据库连接成功！结果: {list(result)}"
    except Exception as e:
        return f"数据库连接失败: {str(e)}"


# 路由定义 - 前端页面
@app.route('/')
def index():
    return render_template('login.html')


@app.route('/dashboard')
@jwt_required(optional=True)
def dashboard():
    if not get_jwt_identity():
        return redirect(url_for('index'))
    return render_template('dashboard.html')


@app.route('/customers')
@jwt_required(optional=True)
def customers():
    if not get_jwt_identity():
        return redirect(url_for('index'))
    return render_template('customers.html')


@app.route('/cards')
@jwt_required(optional=True)
def cards():
    if not get_jwt_identity():
        return redirect(url_for('index'))
    return render_template('cards.html')


@app.route('/transactions')
@jwt_required(optional=True)
def transactions():
    if not get_jwt_identity():
        return redirect(url_for('index'))
    return render_template('transactions.html')


# 路由定义 - API接口
@app.route('/api/auth/login', methods=['POST'])
def login():
    data = request.get_json()
    username = data.get('username')
    password = data.get('password')

    user = User.query.filter_by(username=username).first()
    if not user or not check_password_hash(user.password_hash, password):
        return jsonify({"msg": "认证失败"}), 401

    access_token = create_access_token(identity=user.id)
    return jsonify(access_token=access_token, user_id=user.id, role=user.role)


@app.route('/api/customer/customers', methods=['GET'])
@jwt_required()
def get_customers():
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('per_page', 10, type=int)
    search = request.args.get('search', '')

    query = Customer.query

    if search:
        query = query.filter(
            Customer.customer_name.like(f'%{search}%') |
            Customer.id_card.like(f'%{search}%') |
            Customer.phone.like(f'%{search}%')
        )

    customers = query.paginate(page=page, per_page=per_page, error_out=False)

    return jsonify({
        "items": [{
            'customer_id': c.customer_id,
            'customer_name': c.customer_name,
            'id_card': c.id_card,
            'phone': c.phone,
            'address': c.address,
            'email': c.email
        } for c in customers.items],
        "total": customers.total,
        "pages": customers.pages,
        "current_page": customers.page
    })


@app.route('/api/customer/customers', methods=['POST'])
@jwt_required()
def create_customer():
    data = request.get_json()

    required_fields = ['customer_name', 'id_card', 'phone']
    for field in required_fields:
        if not data.get(field):
            return jsonify({"msg": f"{field} 是必填字段"}), 400

    if Customer.query.filter_by(id_card=data['id_card']).first():
        return jsonify({"msg": "该身份证已注册"}), 400

    new_customer = Customer(
        customer_name=data['customer_name'],
        id_card=data['id_card'],
        phone=data['phone'],
        address=data.get('address'),
        email=data.get('email')
    )

    db.session.add(new_customer)
    db.session.commit()

    return jsonify({
        'customer_id': new_customer.customer_id,
        'customer_name': new_customer.customer_name,
        'id_card': new_customer.id_card,
        'phone': new_customer.phone,
        'address': new_customer.address,
        'email': new_customer.email
    }), 201


@app.route('/api/customer/customers/<int:customer_id>', methods=['PUT'])
@jwt_required()
def update_customer(customer_id):
    customer = Customer.query.get_or_404(customer_id)
    data = request.get_json()

    for key, value in data.items():
        if hasattr(customer, key):
            setattr(customer, key, value)

    db.session.commit()
    return jsonify({
        'customer_id': customer.customer_id,
        'customer_name': customer.customer_name,
        'id_card': customer.id_card,
        'phone': customer.phone,
        'address': customer.address,
        'email': customer.email
    })


@app.route('/api/customer/customers/<int:customer_id>', methods=['DELETE'])
@jwt_required()
def delete_customer(customer_id):
    customer = Customer.query.get_or_404(customer_id)

    if customer.cards:
        return jsonify({"msg": "该客户有相关银行卡，无法删除"}), 400

    db.session.delete(customer)
    db.session.commit()
    return jsonify({"msg": "客户删除成功"})


@app.route('/api/card/cards', methods=['GET'])
@jwt_required()
def get_cards():
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('per_page', 10, type=int)
    customer_id = request.args.get('customer_id', type=int)
    search = request.args.get('search', '')

    query = Card.query

    if customer_id:
        query = query.filter_by(customer_id=customer_id)

    if search:
        query = query.filter(
            Card.card_number.like(f'%{search}%') |
            Card.customer.has(Customer.customer_name.like(f'%{search}%'))
        )

    cards = query.paginate(page=page, per_page=per_page, error_out=False)

    return jsonify({
        "items": [{
            'card_id': c.card_id,
            'card_number': c.card_number,
            'customer_id': c.customer_id,
            'customer_name': c.customer.customer_name,
            'deposit_type': c.deposit_type,
            'balance': c.balance,
            'open_date': c.open_date.strftime("%Y-%m-%d")
        } for c in cards.items],
        "total": cards.total,
        "pages": cards.pages,
        "current_page": cards.page
    })


@app.route('/api/card/cards', methods=['POST'])
@jwt_required()
def create_card():
    data = request.get_json()

    required_fields = ['customer_id', 'deposit_type']
    for field in required_fields:
        if not data.get(field):
            return jsonify({"msg": f"{field} 是必填字段"}), 400

    customer = Customer.query.get_or_404(data['customer_id'])

    random_part = ''.join(random.choices(string.digits, k=8))
    card_number = f"10103576{random_part}"

    if Card.query.filter_by(card_number=card_number).first():
        return jsonify({"msg": "卡号生成失败，请重试"}), 400

    initial_balance = data.get('initial_balance', 0.0)
    if initial_balance < 1:
        return jsonify({"msg": "初始存款不能少于1元"}), 400

    new_card = Card(
        card_number=card_number,
        customer_id=data['customer_id'],
        deposit_type=data['deposit_type'],
        balance=initial_balance,
        password="888888"
    )

    db.session.add(new_card)
    db.session.commit()

    return jsonify({
        'card_id': new_card.card_id,
        'card_number': new_card.card_number,
        'customer_id': new_card.customer_id,
        'deposit_type': new_card.deposit_type,
        'balance': new_card.balance,
        'open_date': new_card.open_date.strftime("%Y-%m-%d")
    }), 201


@app.route('/api/card/cards/<string:card_number>', methods=['GET'])
@jwt_required()
def get_card(card_number):
    card = Card.query.filter_by(card_number=card_number).first_or_404()
    return jsonify({
        'card_id': card.card_id,
        'card_number': card.card_number,
        'customer_id': card.customer_id,
        'customer_name': card.customer.customer_name,
        'deposit_type': card.deposit_type,
        'balance': card.balance,
        'open_date': card.open_date.strftime("%Y-%m-%d")
    })


@app.route('/api/card/cards/<string:card_number>/deposit', methods=['POST'])
@jwt_required()
def deposit(card_number):
    card = Card.query.filter_by(card_number=card_number).first_or_404()
    data = request.get_json()

    amount = data.get('amount')
    if not amount or amount <= 0:
        return jsonify({"msg": "存款金额必须大于0"}), 400

    card.balance += amount

    new_transaction = Transaction(
        card_number=card_number,
        transaction_type="存入",
        transaction_amount=amount,
        remark="存款"
    )
    db.session.add(new_transaction)

    db.session.commit()
    return jsonify({
        "msg": "存款成功",
        "new_balance": card.balance
    })


@app.route('/api/card/cards/<string:card_number>/withdraw', methods=['POST'])
@jwt_required()
def withdraw(card_number):
    card = Card.query.filter_by(card_number=card_number).first_or_404()
    data = request.get_json()

    amount = data.get('amount')
    password = data.get('password')

    if not amount or amount <= 0:
        return jsonify({"msg": "取款金额必须大于0"}), 400

    if not password or card.password != password:
        return jsonify({"msg": "密码错误"}), 401

    if card.balance < amount + 1:
        return jsonify({"msg": "余额不足"}), 400

    card.balance -= amount

    new_transaction = Transaction(
        card_number=card_number,
        transaction_type="支取",
        transaction_amount=amount,
        remark="取款"
    )
    db.session.add(new_transaction)

    db.session.commit()
    return jsonify({
        "msg": "取款成功",
        "new_balance": card.balance
    })


@app.route('/api/transaction/transactions', methods=['GET'])
@jwt_required()
def get_transactions():
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('per_page', 10, type=int)
    card_number = request.args.get('card_number')
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')

    query = Transaction.query

    if card_number:
        query = query.filter_by(card_number=card_number)

    if start_date:
        query = query.filter(Transaction.transaction_date >= start_date)

    if end_date:
        query = query.filter(Transaction.transaction_date <= end_date)

    transactions = query.order_by(Transaction.transaction_date.desc()).paginate(
        page=page, per_page=per_page, error_out=False
    )

    return jsonify({
        "items": [{
            'transaction_id': t.transaction_id,
            'card_number': t.card_number,
            'transaction_type': t.transaction_type,
            'transaction_amount': t.transaction_amount,
            'transaction_date': t.transaction_date.strftime("%Y-%m-%d %H:%M:%S"),
            'remark': t.remark
        } for t in transactions.items],
        "total": transactions.total,
        "pages": transactions.pages,
        "current_page": transactions.page
    })


@app.route('/api/transaction/transfer', methods=['POST'])
@jwt_required()
def transfer():
    data = request.get_json()

    from_card_number = data.get('from_card_number')
    to_card_number = data.get('to_card_number')
    amount = data.get('amount')
    password = data.get('password')

    required_fields = ['from_card_number', 'to_card_number', 'amount', 'password']
    for field in required_fields:
        if not data.get(field):
            return jsonify({"msg": f"{field} 是必填字段"}), 400

    if amount <= 0:
        return jsonify({"msg": "转账金额必须大于0"}), 400

    from_card = Card.query.filter_by(card_number=from_card_number).first_or_404()

    if from_card.password != password:
        return jsonify({"msg": "密码错误"}), 401

    to_card = Card.query.filter_by(card_number=to_card_number).first_or_404()

    if from_card.balance < amount + 1:
        return jsonify({"msg": "余额不足"}), 400

    try:
        from_card.balance -= amount

        new_transaction_from = Transaction(
            card_number=from_card_number,
            transaction_type="支取",
            transaction_amount=amount,
            remark=f"转账到{to_card_number}"
        )
        db.session.add(new_transaction_from)

        to_card.balance += amount

        new_transaction_to = Transaction(
            card_number=to_card_number,
            transaction_type="存入",
            transaction_amount=amount,
            remark=f"接收来自{from_card_number}的转账"
        )
        db.session.add(new_transaction_to)

        db.session.commit()
        return jsonify({
            "msg": "转账成功",
            "from_balance": from_card.balance,
            "to_balance": to_card.balance
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({"msg": f"转账失败: {str(e)}"}), 500


@app.route('/api/dashboard/overview', methods=['GET'])
@jwt_required()
def dashboard_overview():
    customer_count = Customer.query.count()
    card_count = Card.query.count()

    today = datetime.datetime.now().date()
    tomorrow = today + datetime.timedelta(days=1)
    today_transactions = Transaction.query.filter(
        Transaction.transaction_date >= today,
        Transaction.transaction_date < tomorrow
    ).count()

    system_balance = db.session.query(db.func.sum(Card.balance)).scalar() or 0

    recent_transactions = Transaction.query.order_by(Transaction.transaction_date.desc()).limit(5).all()

    return jsonify({
        "customer_count": customer_count,
        "card_count": card_count,
        "today_transactions": today_transactions,
        "system_balance": system_balance,
        "recent_transactions": [{
            'transaction_id': t.transaction_id,
            'card_number': t.card_number,
            'transaction_type': t.transaction_type,
            'transaction_amount': t.transaction_amount,
            'transaction_date': t.transaction_date.strftime("%Y-%m-%d %H:%M:%S"),
            'remark': t.remark
        } for t in recent_transactions]
    })


if __name__ == '__main__':
    # 创建数据库表
    with app.app_context():
        db.create_all()

        # 创建管理员账户（仅首次运行时）
        if not User.query.filter_by(username='admin').first():
            admin = User(
                username='admin',
                password_hash=generate_password_hash('admin123'),  # 修改为password_hash
                role='admin'
            )
            db.session.add(admin)
            db.session.commit()

    app.run(debug=True)